package cn.com.generaldata.os.service.impl;

import java.util.List;
import java.util.Map;

import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.impl.sql.SqlTemplate;
import org.nutz.dao.util.cri.Exps;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.com.generaldata.jsme_indicator_system.entity.IndicatorProvince;
import cn.com.generaldata.jsme_indicator_system.entity.ReportTypeIndicator;
import cn.com.generaldata.jsme_indicator_system.entity.ReportVersion;
import cn.com.generaldata.jsme_indicator_system.util.Constants;
import cn.com.generaldata.jsme_indicator_system.util.Constants.ReportStatSystem;
import cn.com.generaldata.os.vo.IndicatorStatQueryVO;
import cn.com.generaldata.os.vo.IndicatorStatResult;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

/**
 * 指标汇总Service.
 */
@Service
public class IndicatorStatService {

	@Autowired
	private Dao indicatorSystemDao;

	@Autowired
	private SqlTemplate indicatorSystemSqlTemplate;

	@Autowired
	private SqlTemplate sqlTemplate;

	@Autowired
	private UnitService unitService;

	public List<IndicatorStatResult> indicatorStat(IndicatorStatQueryVO queryVO) {
		List<ReportTypeIndicator> indicators = getStatIndicators(queryVO);

		List<IndicatorStatResult> results = Lists.newArrayList();
		for (ReportTypeIndicator indicatorRel : indicators) {
			IndicatorStatResult result = new IndicatorStatResult();
			result.setIndicatorValue(statColumnIndicator(queryVO, indicatorRel));

			IndicatorProvince indicator = indicatorSystemDao.fetch(IndicatorProvince.class,
					indicatorRel.getIndicatorId());

			result.setIndicatorName(indicator.getName());
			result.setIndicatorUnit(indicator.getUnit());
			results.add(result);
		}
		return results;
	}

	private String statColumnIndicator(IndicatorStatQueryVO indicatorStatVO, ReportTypeIndicator reportTypeindicator) {
		String sql = "SELECT SUM(rd.$clomun) FROM "
				+ " tbl_report r INNER JOIN tbl_report_detail rd ON r.id = rd.report_id "
				+ " INNER JOIN tbl_user u ON r.report_user_id = u.id "
				+ " INNER JOIN tbl_report_unit ru ON ru.report_id = r.id AND ru.operator_unit = @provinceUnitId AND ru.check_status = '5' "
				+ " INNER JOIN tbl_unit unit ON u.user_unit = unit.id "
				+ " INNER JOIN tbl_area area ON unit.unit_area_id = area.id "
				+ " WHERE r.report_type_id = @reportTypeId AND r.report_check_status = '5' AND rd.row_index = @row ";
		Map<String, Object> params = indicatorStatVOToParamMap(indicatorStatVO);
		params.put("provinceUnitId", unitService.getProvinceOceanUnit().getId());
		params.put("row", reportTypeindicator.getRow());
		params.put("reportTypeId", reportTypeindicator.getReportTypeId());
		// 汇总时间频率条件SQL
		if (indicatorStatVO.getFrequency() == Constants.REPORT_TIME_MONTH) {
			sql += " AND r.report_timetype = @reportTimetype AND r.report_year = @reportYear AND r.month = @month ";
		} else if (indicatorStatVO.getFrequency() == Constants.REPORT_TIME_YEAR) {
			sql += " AND r.report_timetype = @reportTimetype AND r.report_year = @reportYear AND r.report_year_time = @reportYearTime ";
		} else {
			sql += " AND r.report_timetype = @reportTimetype AND r.report_year = @reportYear AND r.quarter = @quarter ";
		}

		// 数据来源SQL判断
		if (indicatorStatVO.getDatasource() != null) {
			if (indicatorStatVO.getDatasource().intValue() == Constants.INDICATOR_DATASOURCE_UNIT) {
				if (indicatorStatVO.getUnitLevel().intValue() == Constants.UNIT_SCOPE_CURRENT_LEVEL) {
					sql += " AND unit.unit_area_id = @unitAreaId AND u.user_role_id = '3'";
					params.put("unitAreaId", indicatorStatVO.getQueryAreaId());
				} else {
					sql += " AND unit.unit_area_id LIKE @unitAreaId";
					params.put("unitAreaId", indicatorStatVO.getQueryAreaIdPrefix() + "%");
				}
			} else {
				sql += " AND u.user_role_id IN ('4','5') AND area.parent_id = @areaParentId";
				params.put("areaParentId", indicatorStatVO.getQueryAreaId());
			}
		}

		Map<String, Object> vars = Maps.newHashMap();
		vars.put("clomun", "f" + reportTypeindicator.getTheColumn().intValue());

		String result = sqlTemplate.queryForObject(sql, vars, params, String.class);

		return result;
	}

	private Map<String, Object> indicatorStatVOToParamMap(IndicatorStatQueryVO indicatorStatVO) {
		Map<String, Object> params = Maps.newHashMap();

		if (indicatorStatVO.getFrequency() == Constants.REPORT_TIME_MONTH) {
			params.put("reportTimetype", "0");
			params.put("reportYear", indicatorStatVO.getYear());
			params.put("month", indicatorStatVO.getMonth());
		} else if (indicatorStatVO.getFrequency() == Constants.REPORT_TIME_YEAR) {
			params.put("reportTimetype", "1");
			params.put("reportYear", indicatorStatVO.getYear());
			params.put("reportYearTime", indicatorStatVO.getYearTime());
		} else {
			params.put("reportTimetype", "2");
			params.put("reportYear", indicatorStatVO.getYear());
			params.put("quarter", indicatorStatVO.getQuarter());
		}

		return params;
	}

	/**
	 * 获取需要进行汇总的指标.
	 * 
	 * @param queryVO 指标查询条件对象
	 * @return 需要汇总的指标集合
	 */
	private List<ReportTypeIndicator> getStatIndicators(IndicatorStatQueryVO queryVO) {
		Cnd reportVersionCnd = Cnd.where("startYear", "<=", queryVO.getYear()).and("endYear", ">=", queryVO.getYear())
				.and("statSystem", "=", ReportStatSystem.STAT);
		ReportVersion rv = indicatorSystemDao.fetch(ReportVersion.class, reportVersionCnd);

		String sql = "SELECT rtip.* FROM "
				+ "t_indicator_province ip INNER JOIN  t_report_type_indicator rtip ON ip.id = rtip.indicator_id "
				+ "INNER JOIN tbl_report_type trt ON rtip.report_type_id = trt.id AND trt.version_id = @versionId "
				+ "WHERE ip.frequency = @frequency AND ip.code LIKE @codePrefix ";

		Map<String, Object> params = Maps.newHashMap();
		params.put("versionId", rv.getId());
		Integer frequency = Constants.REPORT_INDICATOR_FREQUENCY.get(queryVO.getFrequency());
		params.put("frequency", frequency);
		params.put("codePrefix", queryVO.getIndicatorCodePrefix() + "%");

		return indicatorSystemSqlTemplate.query(sql, params, indicatorSystemDao.getEntity(ReportTypeIndicator.class));
	}

	public List<IndicatorProvince> getIndicatorCategory(Integer parentId) {
		List<IndicatorProvince> result;
		if (parentId == null || parentId == 0) {
			result = indicatorSystemDao.query(IndicatorProvince.class, Cnd.where(Exps.isNull("pid")));
		} else {
			Cnd cnd = Cnd.where("pid", "=", parentId);
			result = indicatorSystemDao.query(IndicatorProvince.class, cnd);
		}
		return result;
	}
}
